import pandas as pd
from util import mysql_utils
from util import dict_data_utils
from concurrent.futures import ThreadPoolExecutor
import time

pool = mysql_utils.get_mysql_pool()

big_batch_dict = dict_data_utils.get_big_batch_dict()
small_batch_dict = dict_data_utils.get_small_batch_dict()
nature_name_dict = dict_data_utils.get_nature_name_dict()
major_group_dict = dict_data_utils.get_major_group_dict()
type_dict = dict_data_utils.get_type_dict()


# 根据 大批次、 小批次、 院校代码 查询院校
def query_university(big_batch, small_batch, code):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f"SELECT province_code, city_code, province, city, nature_name, f211, f985, dual_class_name FROM university_2_new WHERE big_batch={big_batch} AND small_batch={small_batch} AND code={code}"
            cursor.execute(sql_query)
            result = cursor.fetchone()
            # 将查询结果转换为字典
            if result is not None:
                result_dict = dict(zip(['province_code', 'city_code', 'province', 'city', 'nature_name', 'f211', 'f985',
                                        'dual_class_name'], result))
                return result_dict
            else:
                return None
    except Exception as e:
        print(f'查询院校基本信息异常：{e}')
        return None
    finally:
        conn.close()


def query_university_score_rank_new(big_batch, small_batch, code, year, type, major_group):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f"SELECT id FROM university_score_rank_new WHERE big_batch={big_batch} AND small_batch={small_batch} AND code={code} AND year={year} AND type={type} AND major_group={major_group}"
            cursor.execute(sql_query)
            result = cursor.fetchone()
            return result
    except Exception as e:
        print(f'查询university_score_rank_new异常：{e}')
        return None
    finally:
        conn.close()


def insert_university_score_rank_new(insert_values):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            cursor.executemany(
                '''
                INSERT INTO university_score_rank_new (
                    year, big_batch, small_batch, type, major_group_code, major_group, code, name, lowest_score, 
                    lowest_rank, mean_score, mean_rank, plan_number, submit_number, enter_number, province_code, 
                    city_code, province, city, nature_name, f211, f985, dual_class_name, score) 
                VALUES (
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''',
                insert_values
            )
            conn.commit()
            # print('写入university_major_rank_new成功')
    except Exception as e:
        print(f'写入university_major_rank_new异常：{e}')
        return None
    finally:
        conn.close()


def update_university_score_rank_new(update_values):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                '''
                UPDATE university_score_rank_new SET
                    year=%s, big_batch=%s, small_batch=%s, type=%s, major_group_code=%s, major_group=%s, code=%s, name=%s, lowest_score=%s, 
                    lowest_rank=%s, mean_score=%s, mean_rank=%s, plan_number=%s, submit_number=%s, enter_number=%s, province_code=%s, 
                    city_code=%s, province=%s, city=%s, nature_name=%s, f211=%s, f985=%s, dual_class_name=%s, score=%s WHERE id=%s
                ''', update_values)
            conn.commit()
    except Exception as e:
        print(f'更新 university_score_rank_new 异常：{e}')
        return None
    finally:
        conn.close()


def task(row):
    if row['批次'] in big_batch_dict.keys():
        big_batch = big_batch_dict[row['批次']]
    else:
        big_batch = 0

    if row['小批次'] in small_batch_dict.keys():
        small_batch = small_batch_dict[row['小批次']]
    else:
        small_batch = 0

    if row['院校代码'] and not pd.isna(row['院校代码']):
        code = row['院校代码']
    else:
        code = ""

    if row['院校名称'] and not pd.isna(row['院校名称']):
        name = row['院校名称']
    else:
        name = ""

    if row['首选科目'] in type_dict.keys():
        type = type_dict[row['首选科目']]
    else:
        type = 0

    if row['专业组编号'] and not pd.isna(row['专业组编号']):
        major_group_code = row['专业组编号']
    else:
        major_group_code = ""

    if row['专业组名称'] in major_group_dict.keys():
        major_group = major_group_dict[row['专业组名称']]
    else:
        major_group = 0

    if row['2022计划数'] and not pd.isna(row['2022计划数']) and (row['2022计划数'] != "0.0" or row['2022计划数'] != "0") and (
            row['2022计划数'] != 0.0 or row['2022计划数'] != 0):
        try:
            plan_number_2022 = int(row['2022计划数'])
        except Exception as e:
            print(f'2022计划数异常：{e}')
            plan_number_2022 = 0
    else:
        plan_number_2022 = 0

    if row['2022投档数'] and not pd.isna(row['2022投档数']) and (row['2022投档数'] != "0.0" or row['2022投档数'] != "0") and (
            row['2022投档数'] != 0.0 or row['2022投档数'] != 0):
        try:
            submit_number_2022 = int(row['2022投档数'])
        except Exception as e:
            print(f'2022投档数异常：{e}')
            submit_number_2022 = 0
    else:
        submit_number_2022 = 0

    if row['2022录取数'] and not pd.isna(row['2022录取数']) and (row['2022录取数'] != "0.0" or row['2022录取数'] != "0") and (
            row['2022录取数'] != 0.0 or row['2022录取数'] != 0):
        try:
            enter_number_2022 = int(row['2022录取数'])
        except Exception as e:
            print(f'2022录取数异常：{e}')
            enter_number_2022 = 0
    else:
        enter_number_2022 = 0

    if row['2022最低分'] and not pd.isna(row['2022最低分']) and (row['2022最低分'] != "0.0" or row['2022最低分'] != "0") and (
            row['2022最低分'] != 0.0 or row['2022最低分'] != 0):
        try:
            lowest_score_2022 = int(row['2022最低分'])
        except Exception as e:
            print(f'2022最低分异常：{e}')
            lowest_score_2022 = 0
    else:
        lowest_score_2022 = 0

    if row['2022最低分位次'] and not pd.isna(row['2022最低分位次']) and (
            row['2022最低分位次'] != "0.0" or row['2022最低分位次'] != "0") and (
            row['2022最低分位次'] != 0.0 or row['2022最低分位次'] != 0):
        try:
            lowest_rank_2022 = int(row['2022最低分位次'])
        except Exception as e:
            print(f'2022最低分位次异常：{e}')
            lowest_rank_2022 = 0
    else:
        lowest_rank_2022 = 0

    if row['2022平均分'] and not pd.isna(row['2022平均分']) and (row['2022平均分'] != "0.0" or row['2022平均分'] != "0") and (
            row['2022平均分'] != 0.0 or row['2022平均分'] != 0):
        try:
            mean_score_2022 = int(row['2022平均分'])
        except Exception as e:
            print(f'2022平均分异常：{e}')
            mean_score_2022 = 0
    else:
        mean_score_2022 = 0

    if row['2022平均分位次'] and not pd.isna(row['2022平均分位次']) and (
            row['2022平均分位次'] != "0.0" or row['2022平均分位次'] != "0") and (
            row['2022平均分位次'] != 0.0 or row['2022平均分位次'] != 0):
        try:
            mean_rank_2022 = int(row['2022平均分位次'])
        except Exception as e:
            print(f'2022平均分位次异常：{e}')
            mean_rank_2022 = 0
    else:
        mean_rank_2022 = 0

    if '2022正确的分数分布' in row.keys() and row['2022正确的分数分布'] and not pd.isna(row['2022正确的分数分布']):
        score_2022 = row['2022正确的分数分布'].strip()
    else:
        score_2022 = ""

    if row['2021计划数'] and not pd.isna(row['2021计划数']) and (row['2021计划数'] != "0.0" or row['2021计划数'] != "0") and (
            row['2021计划数'] != 0.0 or row['2021计划数'] != 0):
        try:
            plan_number_2021 = int(row['2021计划数'])
        except Exception as e:
            print(f'2021计划数异常：{e}')
            plan_number_2021 = 0
    else:
        plan_number_2021 = 0

    if row['2021投档数'] and not pd.isna(row['2021投档数']) and (row['2021投档数'] != "0.0" or row['2021投档数'] != "0") and (
            row['2021投档数'] != 0.0 or row['2021投档数'] != 0):
        try:
            submit_number_2021 = int(row['2021投档数'])
        except Exception as e:
            print(f'2021投档数异常：{e}')
            submit_number_2021 = 0
    else:
        submit_number_2021 = 0

    if row['2021录取数'] and not pd.isna(row['2021录取数']) and (row['2021录取数'] != "0.0" or row['2021录取数'] != "0") and (
            row['2021录取数'] != 0.0 or row['2021录取数'] != 0):
        try:
            enter_number_2021 = int(row['2021录取数'])
        except Exception as e:
            print(f'2021录取数异常：{e}')
            enter_number_2021 = 0
    else:
        enter_number_2021 = 0

    if row['2021最低分'] and not pd.isna(row['2021最低分']) and (row['2021最低分'] != "0.0" or row['2021最低分'] != "0") and (
            row['2021最低分'] != 0.0 or row['2021最低分'] != 0):
        try:
            lowest_score_2021 = int(row['2021最低分'])
        except Exception as e:
            print(f'2021最低分异常：{e}')
            lowest_score_2021 = 0
    else:
        lowest_score_2021 = 0

    if row['2021最低分位次'] and not pd.isna(row['2021最低分位次']) and (
            row['2021最低分位次'] != "0.0" or row['2021最低分位次'] != "0") and (
            row['2021最低分位次'] != 0.0 or row['2021最低分位次'] != 0):
        try:
            lowest_rank_2021 = int(row['2021最低分位次'])
        except Exception as e:
            print(f'2021最低分位次异常：{e}')
            lowest_rank_2021 = 0
    else:
        lowest_rank_2021 = 0

    if row['2021平均分'] and not pd.isna(row['2021平均分']) and (row['2021平均分'] != "0.0" or row['2021平均分'] != "0") and (
            row['2021平均分'] != 0.0 or row['2021平均分'] != 0):
        try:
            mean_score_2021 = int(row['2021平均分'])
        except Exception as e:
            print(f'2021平均分异常：{e}')
            mean_score_2021 = 0
    else:
        mean_score_2021 = 0

    if row['2021平均分位次'] and not pd.isna(row['2021平均分位次']) and (
            row['2021平均分位次'] != "0.0" or row['2021平均分位次'] != "0") and (
            row['2021平均分位次'] != 0.0 or row['2021平均分位次'] != 0):
        try:
            mean_rank_2021 = int(row['2021平均分位次'])
        except Exception as e:
            print(f'2021平均分位次异常：{e}')
            mean_rank_2021 = 0
    else:
        mean_rank_2021 = 0

    if '2021正确的分数分布' in row.keys() and row['2021正确的分数分布'] and not pd.isna(row['2021正确的分数分布']):
        score_2021 = row['2021正确的分数分布'].strip()
    else:
        score_2021 = ""

    if row['2020计划数'] and not pd.isna(row['2020计划数']) and (row['2020计划数'] != "0.0" or row['2020计划数'] != "0") and (
            row['2020计划数'] != 0.0 or row['2020计划数'] != 0):
        try:
            plan_number_2020 = int(row['2020计划数'])
        except Exception as e:
            print(f'2020计划数异常：{e}')
            plan_number_2020 = 0
    else:
        plan_number_2020 = 0

    if row['2020投档数'] and not pd.isna(row['2020投档数']) and (row['2020投档数'] != "0.0" or row['2020投档数'] != "0") and (
            row['2020投档数'] != 0.0 or row['2020投档数'] != 0):
        try:
            submit_number_2020 = int(row['2020投档数'])
        except Exception as e:
            print(f'2020投档数异常：{e}')
            submit_number_2020 = 0
    else:
        submit_number_2020 = 0

    if row['2020录取数'] and not pd.isna(row['2020录取数']) and (row['2020录取数'] != "0.0" or row['2020录取数'] != "0") and (
            row['2020录取数'] != 0.0 or row['2020录取数'] != 0):
        try:
            enter_number_2020 = int(row['2020录取数'])
        except Exception as e:
            print(f'2020录取数异常：{e}')
            enter_number_2020 = 0
    else:
        enter_number_2020 = 0

    if row['2020最低分'] and not pd.isna(row['2020最低分']) and (row['2020最低分'] != "0.0" or row['2020最低分'] != "0") and (
            row['2020最低分'] != 0.0 or row['2020最低分'] != 0):
        try:
            lowest_score_2020 = int(row['2020最低分'])
        except Exception as e:
            print(f'2020最低分异常：{e}')
            lowest_score_2020 = 0
    else:
        lowest_score_2020 = 0

    if row['2020最低分位次'] and not pd.isna(row['2020最低分位次']) and (
            row['2020最低分位次'] != "0.0" or row['2020最低分位次'] != "0") and (
            row['2020最低分位次'] != 0.0 or row['2020最低分位次'] != 0):
        try:
            lowest_rank_2020 = int(row['2020最低分位次'])
        except Exception as e:
            print(f'2020最低分位次异常：{e}')
            lowest_rank_2020 = 0
    else:
        lowest_rank_2020 = 0

    if row['2020平均分'] and not pd.isna(row['2020平均分']) and (row['2020平均分'] != "0.0" or row['2020平均分'] != "0") and (
            row['2020平均分'] != 0.0 or row['2020平均分'] != 0):
        try:
            mean_score_2020 = int(row['2020平均分'])
        except Exception as e:
            print(f'2020平均分异常：{e}')
            mean_score_2020 = 0
    else:
        mean_score_2020 = 0

    if row['2020平均分位次'] and not pd.isna(row['2020平均分位次']) and (
            row['2020平均分位次'] != "0.0" or row['2020平均分位次'] != "0") and (
            row['2020平均分位次'] != 0.0 or row['2020平均分位次'] != 0):
        try:
            mean_rank_2020 = int(row['2020平均分位次'])
        except Exception as e:
            print(f'2020平均分位次异常：{e}')
            mean_rank_2020 = 0
    else:
        mean_rank_2020 = 0

    if '2020正确的分数分布' in row.keys() and row['2020正确的分数分布'] and not pd.isna(row['2020正确的分数分布']):
        score_2020 = row['2020正确的分数分布'].strip()
    else:
        score_2020 = ""

    # 获取院校信息
    university_info = query_university(big_batch, small_batch, code)
    if university_info:
        province_code = university_info['province_code']
        city_code = university_info['city_code']
        province = university_info['province']
        city = university_info['city']
        nature_name = university_info['nature_name']
        f211 = university_info['f211']
        f985 = university_info['f985']
        dual_class_name = university_info['dual_class_name']
    else:
        province_code = ''
        city_code = ''
        province = ''
        city = ''
        nature_name = 0
        f211 = 0
        f985 = 0
        dual_class_name = 0

    insert_values_2022 = (2022, big_batch, small_batch, type, major_group_code, major_group, code, name, lowest_score_2022,
                    lowest_rank_2022, mean_score_2022, mean_rank_2022, plan_number_2022, submit_number_2022, enter_number_2022, province_code,
                    city_code, province, city, nature_name, f211, f985, dual_class_name, score_2022)
    # print('开始写入2022年数据')
    # print(insert_values_2022)
    # insert_university_major_rank_new(insert_values_2022)

    insert_values_2021 = (2021, big_batch, small_batch, type, major_group_code, major_group, code, name, lowest_score_2021,
                    lowest_rank_2021, mean_score_2021, mean_rank_2021, plan_number_2021, submit_number_2021, enter_number_2021, province_code,
                    city_code, province, city, nature_name, f211, f985, dual_class_name, score_2021)
    # print('开始写入2021年数据')
    # print(insert_values_2021)
    # insert_university_major_rank_new(insert_values_2021)

    insert_values_2020 = (2020, big_batch, small_batch, type, major_group_code, major_group, code, name, lowest_score_2020,
                    lowest_rank_2020, mean_score_2020, mean_rank_2020, plan_number_2020, submit_number_2020, enter_number_2020, province_code,
                    city_code, province, city, nature_name, f211, f985, dual_class_name, score_2020)
    # print('开始写入2020年数据')
    # print(insert_values_2020)
    # insert_university_major_rank_new(insert_values_2020)
    print(f'当前院校：{name}')
    insert_values = [insert_values_2022, insert_values_2021, insert_values_2020]
    print(insert_values)
    insert_university_score_rank_new(insert_values)


if __name__ == "__main__":
    data = pd.read_excel("../static/excel_2024/交杨工-院校专业组的数据-20240517.xlsx", header=0)
    start_time = int(time.time())
    print(f'开始：{start_time}')
    print(f'数据长度：{len(data)}')

    with ThreadPoolExecutor(max_workers=50) as executor:
        futures = [executor.submit(task, row) for index, row in data.iterrows()]
        # for future in futures:
        #     print(future)

    end_time = int(time.time())
    count_time = end_time - start_time
    print(f'结束：{end_time}')
    print(f'耗时：{count_time}')
